package vn.edu.saigontech.ncms.web.offering;

import java.util.ArrayList;
import java.util.Collection;
import java.util.Collections;
import java.util.Comparator;
import java.util.Date;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;

import vn.edu.saigontech.ncms.model.comparator.OfferingMemberComparator;
import vn.edu.saigontech.ncms.model.info.Offering;
import vn.edu.saigontech.ncms.model.info.OfferingMember;
import vn.edu.saigontech.ncms.model.program.Program;
import vn.edu.saigontech.ncms.service.OfferingMemberService;
import vn.edu.saigontech.ncms.service.ProgramService;
import vn.edu.saigontech.ncms.util.DateUtil;
import vn.edu.saigontech.ncms.util.ExcelUtil;
import vn.edu.saigontech.ncms.web.BaseAction;

@SuppressWarnings("serial")
public class TestResultAction extends BaseAction {
	
	private String fromDate;
	private String toDate;
	
	private Date fromDateDate;
	private Date toDateDate;
	
	private int month;
	private int year;
	
	public String showTestResult() {
		int month = DateUtil.getMonth(DateUtil.newDate());
		int year = DateUtil.getYear(DateUtil.newDate());
		this.fromDateDate = DateUtil.getStartDateOfMonth(month, year);
		this.toDateDate = DateUtil.getEndDateOfMonth(month, year);
		REQUEST().setAttribute("fromDate", fromDateDate);
		REQUEST().setAttribute("toDate", toDateDate);
		return SUCCESS;
	}
	
	public String reportTopStudents() {
		Date fromDate = DateUtil.getStartDateOfMonth(month, year);
		Date toDate = DateUtil.getEndDateOfMonth(month, year);
		
		RESPONSE().setContentType("application/vnd.ms-excel");
		RESPONSE().setHeader("Content-disposition","attachment; filename=\""+ "The Best Students - " + this.month + ".xls\"");
		
	 	try {
			HSSFWorkbook workbook = new HSSFWorkbook();
			HSSFSheet sheet = workbook.createSheet("Report");
			
			HSSFFont fontBold18 = ExcelUtil.makeFont(workbook, "Times New Roman", 18, true);
			HSSFFont fontBold12 = ExcelUtil.makeFont(workbook, "Times New Roman", 12, true);
			HSSFFont fontBold11 = ExcelUtil.makeFont(workbook, "Times New Roman", 11, true);
			
			HSSFFont fontNormal11 = ExcelUtil.makeFont(workbook, "Times New Roman", 11, false);
			
			HSSFCellStyle styleCenter_B18 = ExcelUtil.makeCellStyle(workbook, HSSFCellStyle.ALIGN_CENTER, HSSFCellStyle.VERTICAL_CENTER, fontBold18);
			HSSFCellStyle styleCenter_B12 = ExcelUtil.makeCellStyle(workbook, HSSFCellStyle.ALIGN_CENTER, HSSFCellStyle.VERTICAL_CENTER, fontBold12);
			HSSFCellStyle styleCenter_B11 = ExcelUtil.makeCellStyle(workbook, HSSFCellStyle.ALIGN_CENTER, HSSFCellStyle.VERTICAL_CENTER, fontBold11);
			
			HSSFCellStyle styleCenter_N11 = ExcelUtil.makeCellStyle(workbook, HSSFCellStyle.ALIGN_CENTER, HSSFCellStyle.VERTICAL_CENTER, fontNormal11);
			
			int r = 0;
			HSSFRow row = sheet.createRow(r);
			
			HSSFCell cell = row.createCell(0, HSSFCell.CELL_TYPE_STRING);
			cell.setCellStyle(styleCenter_B18);
			row.setHeightInPoints(52);
			cell.setCellValue(new HSSFRichTextString("The Best Students Of Each Program In " + DateUtil.getMonthAsString(fromDate)));
			sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 6));
			
			// BLANK
			r = r + 1;
			row = sheet.createRow(r);
			
			r = r + 1;
			row = sheet.createRow(r);
			row.setHeightInPoints(33);
			
			ExcelUtil.makeColumnLabel(workbook, sheet, row, 0, "No.", 256*5, styleCenter_B12);
			ExcelUtil.makeColumnLabel(workbook, sheet, row, 1, "Program", 256*14, styleCenter_B12);
			ExcelUtil.makeColumnLabel(workbook, sheet, row, 2, "Student", 256*21, styleCenter_B12);
			ExcelUtil.makeColumnLabel(workbook, sheet, row, 3, "Class", 256*12, styleCenter_B12);
			ExcelUtil.makeColumnLabel(workbook, sheet, row, 4, "Ending Date", 256*15, styleCenter_B12);
			ExcelUtil.makeColumnLabel(workbook, sheet, row, 5, "Total Score", 256*14, styleCenter_B12);
			ExcelUtil.makeColumnLabel(workbook, sheet, row, 6, "Absence", 256*12, styleCenter_B12);
			
			// Children New
			Program program = ProgramService.INSTANCE.findById(17l);
			List<OfferingMember> members = OfferingMemberService.INSTANCE.findByDateAndProgram(fromDate, toDate, program);
		 	Collections.sort(members, new OfferingMemberComparator());
		 	int mergeRowSize = 2;
		 	int index = 1;
			for(int i = 0; i <= 2; i++) {
				r = r + 1;
				row = sheet.createRow(r);
				row.setHeightInPoints(35);
				
				OfferingMember member = members.get(i);
				
				cell = row.createCell(0, HSSFCell.CELL_TYPE_NUMERIC);
				cell.setCellStyle(styleCenter_N11);
				cell.setCellValue(index);
				
				if(i == 0) {
					cell = row.createCell(1, HSSFCell.CELL_TYPE_STRING);
					cell.setCellValue(new HSSFRichTextString(program.getName()));
					cell.setCellStyle(styleCenter_B11);
					sheet.addMergedRegion(new CellRangeAddress(r, r + mergeRowSize, 1, 1));
				}
				
				cell = row.createCell(2, HSSFCell.CELL_TYPE_STRING);
				cell.setCellValue(new HSSFRichTextString(member.getReceipt().getStudent().getFullName()));
				cell.setCellStyle(styleCenter_N11);
				
				Offering offering = member.getReceipt().getOffering();
				cell = row.createCell(3, HSSFCell.CELL_TYPE_STRING);
				cell.setCellValue(new HSSFRichTextString(offering.getName()));
				cell.setCellStyle(styleCenter_B11);
				
				String endingDate = DateUtil.toString(offering.getEndDate(), this.i18n("format.date"));
				cell = row.createCell(4, HSSFCell.CELL_TYPE_STRING);
				cell.setCellValue(new HSSFRichTextString(endingDate));
				cell.setCellStyle(styleCenter_B11);
				
				cell = row.createCell(5, HSSFCell.CELL_TYPE_NUMERIC);
				cell.setCellStyle(styleCenter_N11);
				cell.setCellValue(member.getTotalScore());
				
				cell = row.createCell(6, HSSFCell.CELL_TYPE_NUMERIC);
				cell.setCellStyle(styleCenter_N11);
				cell.setCellValue(member.getAbsentAttendances().size());
				index++;
			}
			
			// Teenager New
			program = ProgramService.INSTANCE.findById(12l);
			members = OfferingMemberService.INSTANCE.findByDateAndProgram(fromDate, toDate, program);
		 	Collections.sort(members, new OfferingMemberComparator());
			for(int i = 0; i <= 2; i++) {
				r = r + 1;
				row = sheet.createRow(r);
				row.setHeightInPoints(35);
				
				OfferingMember member = members.get(i);
				
				cell = row.createCell(0, HSSFCell.CELL_TYPE_NUMERIC);
				cell.setCellStyle(styleCenter_N11);
				cell.setCellValue(index);
				
				if(i == 0) {
					cell = row.createCell(1, HSSFCell.CELL_TYPE_STRING);
					cell.setCellValue(new HSSFRichTextString(program.getName()));
					cell.setCellStyle(styleCenter_B11);
					sheet.addMergedRegion(new CellRangeAddress(r, r + mergeRowSize, 1, 1));
				}
				
				cell = row.createCell(2, HSSFCell.CELL_TYPE_STRING);
				cell.setCellValue(new HSSFRichTextString(member.getReceipt().getStudent().getFullName()));
				cell.setCellStyle(styleCenter_N11);
				
				Offering offering = member.getReceipt().getOffering();
				cell = row.createCell(3, HSSFCell.CELL_TYPE_STRING);
				cell.setCellValue(new HSSFRichTextString(offering.getName()));
				cell.setCellStyle(styleCenter_B11);
				
				String endingDate = DateUtil.toString(offering.getEndDate(), this.i18n("format.date"));
				cell = row.createCell(4, HSSFCell.CELL_TYPE_STRING);
				cell.setCellValue(new HSSFRichTextString(endingDate));
				cell.setCellStyle(styleCenter_B11);
				
				cell = row.createCell(5, HSSFCell.CELL_TYPE_NUMERIC);
				cell.setCellStyle(styleCenter_N11);
				cell.setCellValue(member.getTotalScore());
				
				cell = row.createCell(6, HSSFCell.CELL_TYPE_NUMERIC);
				cell.setCellStyle(styleCenter_N11);
				cell.setCellValue(member.getAbsentAttendances().size());
				index++;
			}
			
			// General English New
			program = ProgramService.INSTANCE.findById(13l);
			members = OfferingMemberService.INSTANCE.findByDateAndProgram(fromDate, toDate, program);
		 	Collections.sort(members, new OfferingMemberComparator());
			for(int i = 0; i <= 2; i++) {
				r = r + 1;
				row = sheet.createRow(r);
				row.setHeightInPoints(35);
				
				OfferingMember member = members.get(i);
				
				cell = row.createCell(0, HSSFCell.CELL_TYPE_NUMERIC);
				cell.setCellStyle(styleCenter_N11);
				cell.setCellValue(index);
				
				if(i == 0) {
					cell = row.createCell(1, HSSFCell.CELL_TYPE_STRING);
					cell.setCellValue(new HSSFRichTextString(program.getName()));
					cell.setCellStyle(styleCenter_B11);
					sheet.addMergedRegion(new CellRangeAddress(r, r + mergeRowSize, 1, 1));
				}
				
				cell = row.createCell(2, HSSFCell.CELL_TYPE_STRING);
				cell.setCellValue(new HSSFRichTextString(member.getReceipt().getStudent().getFullName()));
				cell.setCellStyle(styleCenter_N11);
				
				Offering offering = member.getReceipt().getOffering();
				cell = row.createCell(3, HSSFCell.CELL_TYPE_STRING);
				cell.setCellValue(new HSSFRichTextString(offering.getName()));
				cell.setCellStyle(styleCenter_B11);
				
				String endingDate = DateUtil.toString(offering.getEndDate(), this.i18n("format.date"));
				cell = row.createCell(4, HSSFCell.CELL_TYPE_STRING);
				cell.setCellValue(new HSSFRichTextString(endingDate));
				cell.setCellStyle(styleCenter_B11);
				
				cell = row.createCell(5, HSSFCell.CELL_TYPE_NUMERIC);
				cell.setCellStyle(styleCenter_N11);
				cell.setCellValue(member.getTotalScore());
				
				cell = row.createCell(6, HSSFCell.CELL_TYPE_NUMERIC);
				cell.setCellStyle(styleCenter_N11);
				cell.setCellValue(member.getAbsentAttendances().size());
				index++;
			}
			
			workbook.write(RESPONSE().getOutputStream());
		} catch (Exception ex) {
			ex.printStackTrace();
		}
	 	return NONE;
	}
	
	@SuppressWarnings("deprecation")
	public String reportTestResult() {
		this.fromDateDate = DateUtil.fromString(this.fromDate, this.i18n("format.date"));
		this.toDateDate = DateUtil.fromString(this.toDate, this.i18n("format.date")); 
		
		Collection<OfferingMember> offeringMembers = OfferingMemberService.INSTANCE.findByDate(this.fromDateDate, this.toDateDate);
		List<OfferingMember> sorted = new ArrayList<OfferingMember>();
		for (OfferingMember offeringMember : offeringMembers) {
			if(offeringMember.getGrade1() != null) {
				sorted.add(offeringMember);
			}
		}
		Collections.sort(sorted, new Comparator<OfferingMember>() {
			@Override
			public int compare(OfferingMember o1, OfferingMember o2) {
				if(o1.getGrade1().toString().equals(o2.getGrade1().toString())) {
					return o2.getTotalScore1().compareTo(o1.getTotalScore1());
				}
				else {
					return o1.getGrade1().toString().compareTo(o2.getGrade1().toString());
				}
			}
		});
		REQUEST().setAttribute("offeringMembers", sorted);
		
		this.addLoggingDetail(BaseAction.REPORT, "logging.final.report", this.fromDate, this.toDate);
		return SUCCESS;
	}

	public String getFromDate() {
		return fromDate;
	}

	public void setFromDate(String fromDate) {
		this.fromDate = fromDate;
	}

	public String getToDate() {
		return toDate;
	}

	public void setToDate(String toDate) {
		this.toDate = toDate;
	}

	public int getMonth() {
		return month;
	}

	public void setMonth(int month) {
		this.month = month;
	}

	public int getYear() {
		return year;
	}

	public void setYear(int year) {
		this.year = year;
	}
}
